import paramiko
import datetime,time
import json
from django.db.models import Q
from django.shortcuts import render, redirect
from django.views.generic import ListView, TemplateView, View, DetailView
from django.http import HttpResponse, JsonResponse, HttpResponseRedirect
from django.shortcuts import render_to_response,HttpResponse
from django.contrib.auth import login, logout, authenticate
from django.contrib.auth.mixins import LoginRequiredMixin
from django.contrib.auth.models import User
from django.http import FileResponse
from confs.views import get_pagerange
from devops.views import AddMysqlJob,ModifyJob,StopJob,ReStart,ReMove,AddOracleJob
from devops.monitor_items import mysql_job,oracle_job
from dbmonitor.pyora import Checks
from devops.db_api import MySQLdb,MySQLdbD
from dbmonitor.models import MySQLConfig,MySQLHistory,OracleConfig,OracleHistory
from confs.Configs import *

class MySQLListView(LoginRequiredMixin, ListView):
	template_name = 'mysql_list.html'
	model = MySQLConfig
	paginate_by = 10
	ordering = 'id'

	def get(self, request):
		return render_to_response('mysql_list.html', locals())

	def post(self, request):
		ret = {'status': 0}
		dbname = request.POST.get('dbname')
		username = request.POST.get('username')
		password = request.POST.get('password')
		ip = request.POST.get('ip')
		port = int(request.POST.get('port'))
		charset = request.POST.get('charset')
		frequency = int(request.POST.get('frequency'))
		mysql_master_slave = request.POST.get('mysql_master_slave',0)
		status = 1
		MySQLConfig.objects.create(dbname=dbname,username=username,password=password,ip=ip,port=port,charset=charset,status=status,
								   mysql_master_slave=mysql_master_slave)

		#增加定时任务
		s = MySQLConfig.objects.get(dbname=dbname)
		mysql_id = s.id
		flag = 'mysql_' +str(mysql_id)
		AddMysqlJob(mysql_job, frequency, flag, [mysql_id])
		ret['msg'] = 'ok'
		return JsonResponse(ret)

class APIMySQLListView(LoginRequiredMixin, ListView):

	def get(self,request):
		jsondata = {
			"code": 0,
			"msg": "",
			"count": 10,
			"data": [
			]
		}
		newservers = MySQLConfig.objects.all()
		for s in newservers:
			m = {"id": s.id,"dbname":s.dbname,"username":s.username,"ip":s.ip,"port":s.port,"charset":s.charset,
				 "mysql_master_slave":s.mysql_master_slave,"Slave_IO_Running":s.Slave_IO_Running,"Slave_SQL_Running":s.Slave_SQL_Running,
				 "utime":s.utime,"status":s.status}

			jsondata["data"].append(m)

		return JsonResponse(jsondata)

class OracleListView(LoginRequiredMixin, ListView):
	template_name = 'oracle_list.html'
	model = OracleConfig
	paginate_by = 10
	ordering = 'id'

	def get_context_data(self, **kwargs):
		context = super(OracleListView, self).get_context_data(**kwargs)
		context['page_range'] = get_pagerange(context['page_obj'])
		context['frequency_list'] = OracleConfig.frequency_list
		return context

	def post(self, request):
		ret = {'status': 0}
		dbname = request.POST.get('dbname')
		username = request.POST.get('username')
		password = request.POST.get('password')
		ip = request.POST.get('ip')
		port = int(request.POST.get('port'))
		case = request.POST.get('case')
		frequency = int(request.POST.get('frequency'))
		status = 1
		health_status = 1
		OracleConfig.objects.create(dbname=dbname,username=username,password=password,ip=ip,port=port,case=case,status=status,health_status=health_status)

		#增加定时任务
		s = OracleConfig.objects.get(dbname=dbname)
		oracle_id = s.id

		flag = 'oradb_' +str(oracle_id)
		AddOracleJob(oracle_job, frequency, flag, [oracle_id])
		ret['msg'] = 'ok'
		return JsonResponse(ret)


class MySQLDBEditView(LoginRequiredMixin, TemplateView):
	template_name = 'mysql_list.html'

	def get(self, request):
		obj_servers = NewServer.objects.all()
		obj_persons = LinkPerson.objects.all()
		return render_to_response('chart_create.html', locals())

	def post(self,request):

		name = request.POST.get('name')
		dingurl = request.POST.get('dingurl')
		info = request.POST.get('info')
		memberslist = request.POST.getlist('members')
		lgp = LinkGroup.objects.create(name=name,dingurl=dingurl,info=info)

		#多对多添加
		for user_id in memberslist:
			lp = LinkPerson.objects.get(id=user_id)
			lgp.gl.add(lp)
		lgp.save()

		return HttpResponseRedirect('/alert/alert_linkgroup')

class MySQLDBStartView(LoginRequiredMixin, TemplateView):

	def post(self,request):
		ret = {'status': 0}
		id = request.POST.get('mysql_id')
		flag = 'mysql_'+ str(id)
		ReStart(flag)
		s = MySQLConfig.objects.get(id=id)
		s.status = 1
		s.save()
		ret['msg'] = 'ok'
		return JsonResponse(ret)

class OracleDBStartView(LoginRequiredMixin, TemplateView):

	def post(self,request):
		ret = {'status': 0}
		id = request.POST.get('oracle_id')
		flag = 'oradb_'+ str(id)
		ReStart(flag)
		s = OracleConfig.objects.get(id=id)
		s.status = 1
		s.save()
		ret['msg'] = 'ok'
		return JsonResponse(ret)

class MySQLDBStopView(LoginRequiredMixin, TemplateView):

	def post(self,request):
		ret = {'status': 0}
		id = request.POST.get('mysql_id')
		flag = 'mysql_' + str(id)
		StopJob(flag)
		s = MySQLConfig.objects.get(id=id)
		s.status = 0
		s.save()
		ret['msg'] = 'ok'
		return JsonResponse(ret)

class OracleDBStopView(LoginRequiredMixin, TemplateView):

	def post(self,request):
		ret = {'status': 0}
		id = request.POST.get('oracle_id')
		flag = 'oradb_' + str(id)
		StopJob(flag)
		s = OracleConfig.objects.get(id=id)
		s.status = 0
		s.save()
		ret['msg'] = 'ok'
		return JsonResponse(ret)

class MySQLDBDeleteView(LoginRequiredMixin, TemplateView):

	def post(self,request):
		ret = {'status': 0}
		id = request.POST.get('mysql_id')
		flag = 'mysql_' + str(id)
		ReMove(flag)
		s = MySQLConfig.objects.get(id=id)
		s.delete()
		ret['msg'] = 'ok'
		return JsonResponse(ret)

class OracleDBDeleteView(LoginRequiredMixin, TemplateView):

	def post(self,request):
		ret = {'status': 0}
		id = request.POST.get('oracle_id')
		flag = 'oradb_' + str(id)
		ReMove(flag)
		s = OracleConfig.objects.get(id=id)
		s.delete()
		ret['msg'] = 'ok'
		return JsonResponse(ret)

class MySQLDBDetailView(LoginRequiredMixin, ListView):
	template_name = 'mysql_detail.html'
	model = MySQLHistory
	paginate_by = 10
	ordering = 'id'

	def get(self, request):
		id = request.GET.get('id')
		slice = request.GET.get('slice','')
		if slice:
			sl = int(slice)
		else:
			sl = 0
		#时间范围查找
		time_slice = MySQLConfig.time_slice
		nowtime = datetime.datetime.now()

		for t in time_slice:
			if t[0] == sl:
				tn = t[2]

		if sl in range(0,5):
			delta = datetime.timedelta(hours=tn)
		else:
			delta = datetime.timedelta(days=tn)

		starttime = nowtime - delta
		endtime = nowtime

		server = MySQLConfig.objects.get(id=id)
		hostname = server.dbname
		total_qps_list = []
		insert_qps_list = []
		delete_qps_list = []
		update_qps_list = []
		select_qps_list = []
		commit_tps_list = []
		rollback_tps_list = []
		#连接数
		threads_running_list = []
		threads_cached_list = []
		threads_connected_list = []
		threads_created_list = []
		#流量
		bytes_sent_list = []
		bytes_received_list = []
		#Innodb IO
		innodb_buffer_pool_reads_list = []
		Innodb_buffer_pool_pages_flushed_list = []
		#key buffer
		key_buffer_read_rate_list = []
		key_buffer_write_rate_list = []
		key_blocks_used_rate_list = []


		objlst = MySQLHistory.objects.filter(mysqlid=id)
		objlist = objlst.filter(utime__range=[starttime, endtime])


		for ch in objlist:
			dtime = ch.utime
			un_time = time.mktime(dtime.timetuple())
			un_time = int(un_time)
			total_qps_list.append({"timeline": un_time,"value":ch.total_qps})
			insert_qps_list.append({"timeline": un_time, "value": ch.insert_qps})
			select_qps_list.append({"timeline": un_time, "value": ch.select_qps})
			delete_qps_list.append({"timeline": un_time, "value": ch.delete_qps})
			update_qps_list.append({"timeline": un_time, "value": ch.update_qps})
			commit_tps_list.append({"timeline": un_time, "value": ch.commit_tps})
			rollback_tps_list.append({"timeline": un_time, "value": ch.rollback_tps})
			threads_running_list.append({"timeline": un_time, "value": ch.threads_running})
			threads_cached_list.append({"timeline": un_time, "value": ch.threads_cached})
			threads_connected_list.append({"timeline": un_time, "value": ch.threads_connected})
			threads_created_list.append({"timeline": un_time, "value": ch.threads_created})
			bytes_sent_list.append({"timeline": un_time, "value": ch.bytes_sent})
			bytes_received_list.append({"timeline": un_time, "value": ch.bytes_received})
			innodb_buffer_pool_reads_list.append({"timeline": un_time, "value": ch.innodb_buffer_pool_reads})
			Innodb_buffer_pool_pages_flushed_list.append({"timeline": un_time, "value": ch.Innodb_buffer_pool_pages_flushed})
			key_buffer_read_rate_list.append({"timeline": un_time, "value": ch.key_buffer_read_rate})
			key_buffer_write_rate_list.append({"timeline": un_time, "value": ch.key_buffer_write_rate})
			key_blocks_used_rate_list.append({"timeline": un_time, "value": ch.key_blocks_used_rate})


		#key buffer
		key_buffer_read_rate_list = json.dumps(key_buffer_read_rate_list)
		key_buffer_write_rate_list = json.dumps(key_buffer_write_rate_list)
		key_blocks_used_rate_list = json.dumps(key_blocks_used_rate_list)
		#Innodb IO
		innodb_buffer_pool_reads_list = json.dumps(innodb_buffer_pool_reads_list)
		Innodb_buffer_pool_pages_flushed_list = json.dumps(Innodb_buffer_pool_pages_flushed_list)

		#流量
		bytes_sent_list = json.dumps(bytes_sent_list)
		bytes_received_list = json.dumps(bytes_received_list)

		total_qps_list = json.dumps(total_qps_list)
		insert_qps_list = json.dumps(insert_qps_list)
		select_qps_list = json.dumps(select_qps_list)
		delete_qps_list = json.dumps(delete_qps_list)
		update_qps_list = json.dumps(update_qps_list)
		commit_tps_list = json.dumps(commit_tps_list)
		rollback_tps_list = json.dumps(rollback_tps_list)
		#连接数
		threads_running_list = json.dumps(threads_running_list)
		threads_cached_list = json.dumps(threads_cached_list)
		threads_connected_list = json.dumps(threads_connected_list)
		threads_created_list = json.dumps(threads_created_list)




		#监控指标图表
		ecslist = [
			(total_qps_list, 'total_qps', 'Questions QPS', 'red', 'Questions QPS'),
			(insert_qps_list,'insert_qps','插入QPS','green','插入QPS'),
			(select_qps_list,'select_qps','查询QPS','yellow','查询QPS'),
			(delete_qps_list, 'delete_qps', '删除QPS', 'plum', '删除QPS'),
			(update_qps_list, 'update_qps', '修改QPS', 'plum', '修改QPS')
		]

		#文件数
		my = MySQLConfig.objects.get(id=id)
		username = my.username
		password = my.password
		ip = my.ip
		port = my.port
		charset = my.charset
		mysqldb = MySQLdb(username=username, password=password, ip=ip, port=port, charset=charset)
		rs1 = mysqldb.GetStatus()
		rs2 = mysqldb.open_files_limit()
		rs3 = mysqldb.max_connections()
		rs4 = mysqldb.table_open_cache()


		open_files = json.dumps(int(rs1['Open_files']))
		open_files_limit = json.dumps(int(rs2['open_files_limit']))

		open_tables = json.dumps(int(rs1['Open_tables']))
		table_open_cache = json.dumps(int(rs4['table_open_cache'])-int(rs1['Open_tables']))

		s_Threads_connected = json.dumps(int(rs1['Threads_connected']))
		s_open_files_limit = json.dumps(int(rs3['max_connections'])-int(rs1['Threads_connected']))



		#print (type(slice))
		if server.mysql_master_slave == 2:
			slave = mysqldb.SlaveStatus()
			Master_Log_File = slave['Master_Log_File']
			Read_Master_Log_Pos = slave['Read_Master_Log_Pos']
			Relay_Master_Log_File = slave['Relay_Master_Log_File']
			Exec_Master_Log_Pos = slave['Exec_Master_Log_Pos']
			Slave_IO_Running = slave['Slave_IO_Running']
			Slave_SQL_Running = slave['Slave_SQL_Running']

			return render_to_response('mysql_slave_detail.html', locals())

		return render_to_response('mysql_detail.html', locals())


class OracleTableSpaceView(LoginRequiredMixin, ListView):
	template_name = 'oracle_tablespace.html'
	model = OracleHistory
	paginate_by = 10
	ordering = 'id'

	def get(self, request):
		id = request.GET.get('id')
		try:
			rs = OracleHistory.objects.filter(oracleid=id).order_by('-id')[:1][0]
			ecslist = []
			color = ['Green', 'DeepSkyBlue', 'DarkSlateGray', 'Orange', 'Gray', 'Navy', 'SlateGray']
			tblist = json.loads(rs.tablespace)
			num = 0
			for t in tblist:
				TABLESPACE_NAME = t['TABLESPACE_NAME']
				USE_SPACE = round(t['USE_SPACE'],3)
				FREE_SPACE = round(t['FREE_SPACE'],3)
				ecslist.append((TABLESPACE_NAME,'已使用','空闲',USE_SPACE,FREE_SPACE,color[num]))
				num += 1
		except:
			ecslist = []



		return render_to_response('oracle_tablespace.html', locals())

class MySQLSlowLogView(LoginRequiredMixin, TemplateView):
	template_name = 'mysql_list.html'

	def get(self, request):
		#预定义
		type_list = [(1,'最近时间'),(2,'次数'),(3,'查询时间(总计)'),(4,'查询时间(最小)'),(5,'查询时间(最大)'),(6,'锁等待时间(总计)')]
		type_dict = {1:'last_seen',2:'ts_cnt',3:'Query_time_sum',4:'Query_time_min',5:'Query_time_max',6:'Lock_time_sum'}
		sequence_list = [(0,'倒序'),(1,'正序')]
		sequence_dict = {0:'desc',1:'asc'}
		id = request.GET.get('id')
		stime = request.GET.get('stime')
		etime = request.GET.get('etime')
		date = datetime.datetime.now().strftime('%Y-%m-%d')
		stime = '%s 00:00:00' % date if not stime else stime
		etime = '%s 23:59:59' % date if not etime else etime
		type = request.GET.get('type',1)

		sequence = request.GET.get('sequence',0)
		c_type = int(type)
		c_sequence =  int(sequence)
		mys = MySQLConfig.objects.get(id=id)
		#连接数据库
		mysqldb = MySQLdbD(mysql_user,mysql_passwd,mysql_host,mysql_db)
		result = mysqldb.SlowLog(id,stime,etime,type_dict[c_type],sequence_dict[c_sequence])


		return render_to_response('mysql_slowlog.html', locals())

class MySQLSlowLogDetailView(LoginRequiredMixin, TemplateView):
	template_name = 'mysql_slowlog_detail.html'

	def get(self, request):
		checksum = request.GET.get('checksum')
		dbname = request.GET.get('dname')
		#连接数据库
		mysqldb = MySQLdbD(mysql_user,mysql_passwd,mysql_host,mysql_db)
		result = mysqldb.SlowLogDetail(checksum)[0]
		return render_to_response('mysql_slowlog_detail.html', locals())

class OracleDetailView(LoginRequiredMixin, ListView):
	template_name = 'oracle_detail.html'
	model = OracleHistory
	paginate_by = 10
	ordering = 'id'

	def get(self, request):
		id = request.GET.get('id')
		server = OracleConfig.objects.get(id=id)
		username = server.username
		password = server.password
		address = server.ip
		port = server.port
		case = server.case
		db = Checks(username=username,password=password,address=address,port=port,case=case)
		check_active = db.check_active()
		rcachehit = db.rcachehit()


		return render_to_response('oracle_detail.html', locals())